package cn.edu.nwpu.warehousemanagement.dao;

import cn.edu.nwpu.warehousemanagement.constantEnum.OperationType;
import cn.edu.nwpu.warehousemanagement.pojo.InventoryRecord;
import cn.edu.nwpu.warehousemanagement.pojo.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface InventoryRecordRepository extends JpaRepository<InventoryRecord, Long> {

    // ==================== 基础CRUD ====================
    // 继承自JpaRepository:
    // save()       - 创建/更新记录
    // findById()   - 根据ID查询(默认排除已删除)
    // deleteById() - 根据ID软删除
    // findAll()    - 查询所有未删除记录

    // 精确匹配ID
    Optional<InventoryRecord> findByIdAndDeletedFalse(Long id);

    // 查询未删除的记录（分页）
    Page<InventoryRecord> findByDeletedFalse(Pageable pageable);

    // 查询包括已删除的记录
    @Query("SELECT ir FROM InventoryRecord ir WHERE ir.id = :id")
    Optional<InventoryRecord> findByIdIncludeDeleted(@Param("id") Long id);

    // 查询所有记录（包括已删除的）
    @Query("SELECT ir FROM InventoryRecord ir")
    List<InventoryRecord> findAllIncludeDeleted();

    // 物理删除
    @Modifying
    @Transactional
    @Query("DELETE FROM InventoryRecord ir WHERE ir.id = :id")
    void hardDeleteById(@Param("id") Long id);

    // ==================== 按操作类型查询 ====================
    List<InventoryRecord> findByTypeAndDeletedFalse(OperationType type);

    // 按操作类型分页查询（排除已删除）
    Page<InventoryRecord> findByTypeAndDeletedFalse(OperationType type, Pageable pageable);

    // ==================== 按操作员查询 ====================
    List<InventoryRecord> findByOperatorIdAndDeletedFalse(Long operatorId);

    // 查询操作员特定类型的记录（排除已删除）
    List<InventoryRecord> findByOperatorIdAndTypeAndDeletedFalse(Long operatorId, OperationType type);

    // ==================== 按时间范围查询 ====================
    // 查询某时间之后的记录（排除已删除）
    List<InventoryRecord> findByOperationTimeAfterAndDeletedFalse(LocalDateTime startTime);

    // 查询某时间之前的记录（排除已删除）
    List<InventoryRecord> findByOperationTimeBeforeAndDeletedFalse(LocalDateTime endTime);

    // 查询时间区间内的记录（排除已删除）
    List<InventoryRecord> findByOperationTimeBetweenAndDeletedFalse(LocalDateTime startTime, LocalDateTime endTime);

    // 查询某时间段内特定类型的记录（排除已删除）
    List<InventoryRecord> findByTypeAndOperationTimeBetweenAndDeletedFalse(
            OperationType type,
            LocalDateTime startTime,
            LocalDateTime endTime);

    // ==================== 按关联订单查询 ====================
    List<InventoryRecord> findByRelatedOrderIdAndDeletedFalse(Long orderId);

    // 查询有订单关联的记录（排除已删除）
    List<InventoryRecord> findByRelatedOrderIdIsNotNullAndDeletedFalse();

    // 查询无订单关联的记录（排除已删除）
    List<InventoryRecord> findByRelatedOrderIdIsNullAndDeletedFalse();

    // ==================== 关联货品(Freight)查询 ====================
    // 查询包含指定货品的记录（通过中间表inventory_freight，排除已删除）
    @Query("SELECT ir FROM InventoryRecord ir JOIN ir.freights f WHERE f.id = :freightId AND ir.deleted = false")
    List<InventoryRecord> findByFreightId(@Param("freightId") Long freightId);

    // 查询包含指定商品(Product)的记录（多表关联，排除已删除）
    @Query("SELECT DISTINCT ir FROM InventoryRecord ir " +
            "JOIN ir.freights f " +
            "JOIN f.product p " +
            "WHERE p.id = :productId AND ir.deleted = false")
    List<InventoryRecord> findByProductId(@Param("productId") Long productId);

    // 通过FreightId查询关联的InventoryRecord
    @Query("SELECT ir FROM InventoryRecord ir JOIN ir.freights f WHERE f.id = :freightId AND ir.deleted = false")
    Optional<InventoryRecord> findByFreightsIdAndDeletedFalse(@Param("freightId") Long freightId);

    // ==================== 统计方法 ====================
    // 统计某操作类型的记录数（排除已删除）
    long countByTypeAndDeletedFalse(OperationType type);

    // 统计操作员的记录数（排除已删除）
    long countByOperatorIdAndDeletedFalse(Long operatorId);

    // 统计时间段内的操作次数（排除已删除）
    @Query("SELECT COUNT(ir), ir.type FROM InventoryRecord ir " +
            "WHERE ir.operationTime BETWEEN :start AND :end " +
            "AND ir.deleted = false " +
            "GROUP BY ir.type")
    List<Object[]> countOperationsByTypeInPeriod(
            @Param("start") LocalDateTime start,
            @Param("end") LocalDateTime end);

    // ==================== 批量操作 ====================
    // 批量软删除过期的临时记录
    @Modifying
    @Transactional
    @Query("UPDATE InventoryRecord ir SET ir.deleted = true WHERE ir.type = 'TEMP' AND ir.operationTime < :expireTime")
    int softDeleteExpiredTempRecords(@Param("expireTime") LocalDateTime expireTime);

    // 软删除配置
    @Modifying
    @Transactional
    @Query("UPDATE InventoryRecord ir SET ir.deleted = true WHERE ir.id = :id")
    int softDeleteById(@Param("id") Long id);

    // 批量软删除
    @Modifying
    @Transactional
    @Query("UPDATE InventoryRecord ir SET ir.deleted = true WHERE ir.id IN :ids")
    int batchSoftDelete(@Param("ids") List<Long> ids);

    // 恢复软删除的配置
    @Modifying
    @Transactional
    @Query("UPDATE InventoryRecord ir SET ir.deleted = false WHERE ir.id = :id")
    int restoreById(@Param("id") Long id);

    // ==================== 业务相关查询 ====================
    // 获取最近一次某商品的操作记录（排除已删除）
    @Query("SELECT ir FROM InventoryRecord ir " +
            "JOIN ir.freights f " +
            "JOIN f.product p " +
            "WHERE p.id = :productId AND ir.deleted = false " +
            "ORDER BY ir.operationTime DESC LIMIT 1")
    Optional<InventoryRecord> findLatestByProductId(@Param("productId") Long productId);

    // 检查货品是否存在于任何记录中（防止删除被引用的货品，包括已删除记录）
    @Query("SELECT CASE WHEN COUNT(f) > 0 THEN true ELSE false END " +
            "FROM InventoryRecord ir JOIN ir.freights f WHERE f.id = :freightId")
    boolean existsFreightInAnyRecord(@Param("freightId") Long freightId);

    // 检查货品是否存在于任何未删除记录中
    @Query("SELECT CASE WHEN COUNT(f) > 0 THEN true ELSE false END " +
            "FROM InventoryRecord ir JOIN ir.freights f WHERE f.id = :freightId AND ir.deleted = false")
    boolean existsFreightInActiveRecord(@Param("freightId") Long freightId);

    Optional<InventoryRecord> findByBatchIdAndDeletedFalse(String batchId);

    // 获取当天某商品的入库数量
    @Query("SELECT COUNT(f) " +
            "FROM InventoryRecord ir " +
            "JOIN ir.freights f " +
            "JOIN f.product p " +
            "WHERE p.id = :productId " +
            "AND ir.type = 'INBOUND' " +
            "AND ir.operationTime >= :startOfDay " +
            "AND ir.operationTime < :startOfNextDay " +
            "AND ir.deleted = false")
    long countTodayInboundByProductId(
            @Param("productId") Long productId,
            @Param("startOfDay") LocalDateTime startOfDay,
            @Param("startOfNextDay") LocalDateTime startOfNextDay
    );

    // 获取当天某商品的出库数量
    @Query("SELECT COUNT(f) " +
            "FROM InventoryRecord ir " +
            "JOIN ir.freights f " +
            "JOIN f.product p " +
            "WHERE p.id = :productId " +
            "AND ir.type = 'OUTBOUND' " +
            "AND ir.operationTime >= :startOfDay " +
            "AND ir.operationTime < :startOfNextDay " +
            "AND ir.deleted = false")
    long countTodayOutboundByProductId(
            @Param("productId") Long productId,
            @Param("startOfDay") LocalDateTime startOfDay,
            @Param("startOfNextDay") LocalDateTime startOfNextDay
    );

    long count();


    // 通过productName模糊和operationType筛选分页查询库存记录
    @Query("SELECT DISTINCT ir FROM InventoryRecord ir " +
            "JOIN ir.freights f " +
            "JOIN f.product p " +
            "WHERE LOWER(p.name) LIKE LOWER(CONCAT('%', :productName, '%')) " +
            "AND (:operationType IS NULL OR ir.type = :operationType) " +  // 支持操作类型筛选
            "AND ir.deleted = false")
    Page<InventoryRecord> findByProductNameContaining(
            @Param("productName") String productName,
            @Param("operationType") OperationType operationType,  // 新增操作类型参数
            Pageable pageable
    );
}